from fastapi import APIRouter, Query, Depends
from pydantic import Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

check_head_router = APIRouter(prefix="/issue/checkHead", tags=["设备盘点管理"], dependencies=[Depends(oauth2_scheme)])


class CheckSearch(PaginationBase):
    fsiteNo: str = Field(None, description="")
    ftakedateStart: str = Field(None, description="")
    ftakedateEnd: str = Field(None, description="")
    fpointNo: str = Field(None, description="")
    fbillno: str = Field(None, description="")
    fpduser: str = Field(None, description="")
    fbillstate: str = Field(None, description="")


@check_head_router.get("/list", summary="查询设备盘点信息")
def list_check_head(check: CheckSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.s_tc_check_head where 1=1"
    if check.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = check.fsiteNo
    if check.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = check.fpointNo
    if check.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = check.fbillno
    if check.fpduser is not None:
        sql = sql + " and fpduser = :fpduser "
        param["fpduser"] = check.fpduser
    if check.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = check.fbillstate
    if (check.ftakedateStart is not None) and (check.ftakedateEnd is not None):
        sql = sql + " and to_char(fpddate,'yyyymmdd') between :ftakedateStart and  :ftakedateEnd "
        param["ftakedateStart"] = check.ftakedateStart
        param["ftakedateEnd"] = check.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fpduser,fpddate,fpoint_no,fitemcount,fpiececount,ftotalamt,"
    sql = sql + " fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate"
    sql = sql + " from lims.s_tc_check_head where 1=1"
    if check.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = check.fsiteNo
    if check.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = check.fpointNo
    if check.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = check.fbillno
    if check.fpduser is not None:
        sql = sql + " and fpduser = :fpduser "
        param["fpduser"] = check.fpduser
    if check.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = check.fbillstate
    if (check.ftakedateStart is not None) and (check.ftakedateEnd is not None):
        sql = sql + " and to_char(fpddate,'yyyymmdd') between :ftakedateStart and  :ftakedateEnd "
        param["ftakedateStart"] = check.ftakedateStart
        param["ftakedateEnd"] = check.ftakedateEnd
    if check.order == "desc":
        sql = sql + " order by  fpddate,fbillno desc "
    else:
        sql = sql + " order by  fpddate,fbillno "
    sql = sql + " limit :limit offset :offset"
    param["limit"] = check.limit
    param["offset"] = check.offset
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fpoint_size,b.fequipmentbarcode,b.feuipmentid,b.fmacno,b.fequipmenttag,"
    sql_3 = sql_3 + " b.flinkmac,b.flinkmark,b.fbuyprice,b.fpdflag,b.fpduser,b.fpddate"
    sql_3 = sql_3 + " from lims.s_tc_check_head a,lims.s_tc_check_detail b where a.fbillno=b.fbillno "
    if check.fsiteNo is not None:
        sql_3 = sql_3 + " and fsite_no = :site_no "
        param["site_no"] = check.fsiteNo
    if check.fpointNo is not None:
        sql_3 = sql_3 + " and fpoint_no = :fpointNo "
        param["fpointNo"] = check.fpointNo
    if check.fbillno is not None:
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = check.fbillno
    if check.fpduser is not None:
        sql_3 = sql_3 + " and fpduser = :fpduser "
        param["fpduser"] = check.fpduser
    if check.fbillstate is not None:
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param["fbillstate"] = check.fbillstate
    if (check.ftakedateStart is not None) and (check.ftakedateEnd is not None):
        sql_3 = sql_3 + " and to_char(a.fpddate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = check.ftakedateStart
        param["ftakedateEnd"] = check.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fpduser'] = data[key][2]
        d1['fpddate'] = data[key][3]
        d1['fpointno'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fexplanation'] = data[key][8]
        d1['fupddate'] = data[key][9]
        d1['foperator'] = data[key][10]
        d1['fcheckdate'] = data[key][11]
        d1['fcheckuser'] = data[key][12]
        d1['fbillstate'] = data[key][13]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fpointsize'] = data_detail[j1][2]
                d2['fequipmentbarcode'] = data_detail[j1][3]
                d2['feuipmentid'] = data_detail[j1][4]
                d2['fmacno'] = data_detail[j1][5]
                d2['fequipmenttag'] = data_detail[j1][6]
                d2['flinkmac'] = data_detail[j1][7]
                d2['flinkmark'] = data_detail[j1][8]
                d2['fbuyprice'] = data_detail[j1][9]
                d2['fpdflag'] = data_detail[j1][10]
                d2['fpduser'] = data_detail[j1][11]
                d2['fpddate'] = data_detail[j1][12]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": check.limit, "page": check.pageIndex,
                 "pages": (count + check.limit - 1) // check.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
